using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Abs.Core.DAL.AdoNet
{
    internal abstract class AdoNetRepository<T> : Repository<T>
    {
        //Constructor
        /// <summary>
        /// Create new instance of repository.
        /// Every time a new instance created, remember to wrap it in
        /// using statement in order to call Dispose method automatically
        /// when the instance is not used anymore.
        /// </summary>
        internal AdoNetRepository()
        {
            this.SqlConnection = new SqlConnection(DbConfig.ConnectionString);
            this.SqlCommand = new SqlCommand
                              {
                                  CommandType = CommandType.StoredProcedure,
                                  Connection = this.SqlConnection
                              };
        }//end of internal Repository

        //Properties
        protected SqlConnection SqlConnection { get; set; }
        protected SqlCommand SqlCommand { get; set; }
        protected SqlDataReader SqlDataReader { get; set; }

        //CRUD Methods
        public abstract override bool Create(T item);
        public abstract override IList<T> SelectAll();
        public abstract override T Select(object [] id);
        public abstract override bool Update(T item);
        public abstract override bool Delete(object [] id);

        /// <summary>
        /// Dispose SqlConnection, SqlCommand and SqlDataReader.
        /// Call this method whenever this new instance created.
        /// </summary>
        public override void Dispose()
        {
            if (this.SqlConnection != null)
                this.SqlConnection.Dispose();
            if(this.SqlCommand != null)
                this.SqlCommand.Dispose();
            if(this.SqlDataReader != null)
                this.SqlDataReader.Dispose();
        }//end of public void Dispose

        public int GetIdentity(string tableName)
        {
            this.SqlCommand.CommandText = StoredProcedures.GetIdentity;
            this.SqlCommand.Parameters.Add(new SqlParameter("tableName", tableName));

            var identity = this.ExecuteScalar();
            if (identity == null)
                return 0;
            return Convert.ToInt32(identity);
        }//end of public int GetIdentity(string tableName)

        protected void OpenConnection()
        {
            if(this.SqlConnection.State == ConnectionState.Closed)
                this.SqlConnection.Open();
        }//end of protected void OpenConnection()

        protected void CloseConnection()
        {
            if(this.SqlConnection.State == ConnectionState.Open)
                this.SqlConnection.Close();
        }//end of protected void CloseConnection()

        /// <summary>
        /// Either execute CREATE, UPDATE or DELETE
        /// </summary>
        /// <returns>
        ///     Number of rows affected. 
        ///     Return 0 means, failed. 
        ///     Return > 0, success
        /// </returns>
        protected bool ExecuteNonQuery()
        {
            try
            {
                this.OpenConnection();

                //Execute the stored procedures
                var affectedRow = this.SqlCommand.ExecuteNonQuery();

                if (affectedRow > NoChanges)
                    return true;
                return false;
            }//end of try{}
            catch (SqlException sqlException)
            {
                Console.WriteLine(sqlException.Message);
                return false;
            }//end of catch (SqlException sqlException)
            catch (InvalidOperationException invalidOperationException)
            {
                Console.WriteLine(invalidOperationException.Message);
                return false;
            }//end of catch(InvalidOperationException)
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message);
                return false;
            }//end of catch (Exception exception)
        }//end of protected int ExecuteNonQuery(string command)

        /// <summary>
        ///     Retrieve the data from database. In other words,
        ///     use this method for SELECT command.
        /// </summary>
        /// <returns>
        ///     Return new SqlDataReader object if successfully executed.
        ///     Otherwise, return null (Exception occured).
        /// </returns>
        protected SqlDataReader ExecuteReader()
        {
            try
            {
                this.OpenConnection();

                //Execute the stored procedures
                return this.SqlCommand.ExecuteReader();
            }//end of try{}
            catch (SqlException sqlException)
            {
                Console.WriteLine(sqlException.Message);
                return null;
            }//end of catch (SqlException sqlException)
            catch (InvalidOperationException invalidOperationException)
            {
                Console.WriteLine(invalidOperationException.Message);
                return null;
            }//end of catch(InvalidOperationException)
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message);
                return null;
            }//end of catch (Exception exception)
        }//end of protected SqlDataReader ExecuteDataReader()

        public object ExecuteScalar()
        {
            try
            {
                this.OpenConnection();

                //Execute the stored procedures
                return this.SqlCommand.ExecuteScalar();
            }//end of try{}
            catch (SqlException sqlException)
            {
                Console.WriteLine(sqlException.Message);
                return null;
            }//end of catch (SqlException sqlException)
            catch (InvalidOperationException invalidOperationException)
            {
                Console.WriteLine(invalidOperationException.Message);
                return null;
            }//end of catch(InvalidOperationException)
            catch (Exception exception)
            {
                Console.WriteLine(exception.Message);
                return null;
            }//end of catch (Exception exception)
        }//end of public object ExecuteScalar()
    }//end of internal abstract class AdoNetRepository
}//end of namespace